package com.vincent.jvmtest.Jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @author vincent
 * @DATE 2018/11/25
 * @Description
 */
public class AbstractJdbc {
    private static Map<String, DruidDataSource> dataSourceMap = new ConcurrentHashMap<>();
    private static Map<String, JdbcTemplate> jdbcTemplateMap = new ConcurrentHashMap<>();


    public static JdbcTemplate getTemplate() {
//        DruidDataSource druidDataSource = new DruidDataSource();
//        druidDataSource.setUrl("jdbc:mysql://localhost:3306/vincent?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&serverTimezone=UTC");
//        druidDataSource.setUsername("root");
//        druidDataSource.setPassword("123456");
//        // 初始化连接数
//        druidDataSource.setInitialSize(1);
//        // 最小连接数
//        druidDataSource.setMinIdle(1);
//        // 连接最长等待时间
//        druidDataSource.setMaxWait(60000L);
//        // 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
//        druidDataSource.setTestWhileIdle(true);
//        // 最大连接处
//        druidDataSource.setMaxActive(10);
//        // 连接保持空闲而不被驱逐的最小时间
//        druidDataSource.setMinEvictableIdleTimeMillis(60 * 60 * 1000);
//        dataSourceMap.put("t1", druidDataSource);
        return JdbcTemplateManager.instance.getJdbcTemplate("t1");
    }

    public static void init() {
        Map<String, String> config = new HashMap<>();
        config.put("url", "jdbc:mysql://localhost:3306/vincent?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&serverTimezone=UTC");
        config.put("username", "root");
        config.put("password", "123456");
        JdbcTemplateManager.instance.initJdbcTemplate("t1", config);
    }

    public static void main(String[] args) throws SQLException {
        init();
        JdbcTemplate template = getTemplate();
        List<Map<String, Object>> maps = template.queryForList("SELECT  * from userinfo");
        //System.out.println(maps);
        JdbcTemplateManager.instance.changeMaxActive("t1", 10);
        int update = template.update("INSERT INTO userinfo(name, age, password) VALUE (?,?,?)", "haha", 18, "ab,s");
        System.out.println("inset " + update + "rows");
        List<Map<String, Object>> list = template.queryForList("SELECT  * from userinfo WHERE name =? ", "haha");
//
//        int[] ints = template.batchUpdate("INSERT INTO userinfo(name, age, password) VALUE (?,?,?)", new BatchPreparedStatementSetter() {
//            @Override
//            public void setValues(PreparedStatement ps, int i) throws SQLException {
//                for (Map<String, Object> item : list) {
//                    ps.setObject(1, item.get("name"));
//                    ps.setObject(1, item.get("age"));
//                    ps.setObject(1, item.get("password"));
//                }
//            }
//
//            @Override
//            public int getBatchSize() {
//                return list.size();
//            }
//        });
        Connection connection = template.getDataSource().getConnection();
        connection.setAutoCommit(false);
        long start = System.currentTimeMillis();
//        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO userinfo(name, age, password) VALUE (?,?,?)");
//        for (Map<String, Object> item : list) {
//            preparedStatement.setObject(1, item.get("name"));
//            preparedStatement.setObject(1, item.get("age"));
//            preparedStatement.setObject(1, item.get("password"));
//            preparedStatement.addBatch();
//        }
//        connection.commit();
        Statement statement = connection.createStatement();
        int i = 0;
        for (Map<String, Object> item : list) {
            List<Object> valueList = new LinkedList<>();
            valueList.add("'" + item.get("name")+ "'");
            valueList.add(item.get("age"));
            valueList.add("'" + item.get("password") + "'");
            String sql = "INSERT INTO userinfo(name, age, password) VALUE (" + StringUtils.join(valueList, ",") + ")";
            statement.addBatch(sql);
            i++;
            if (i == 1000) {
                statement.executeBatch();
                connection.commit();
            }
        }
        statement.executeBatch();
        connection.commit();
        long end = System.currentTimeMillis();
        System.out.println("insert " + list.size() + "rows cast:" + (end - start));

        long start1 = System.currentTimeMillis();
        Statement statement1 = connection.createStatement();
        int i1 = 0;
        for (Map<String, Object> item : list) {
            List<Object> valueList = new ArrayList<>(item.size());
            valueList.add("'" + item.get("name")+ "'");
            valueList.add(item.get("age"));
            valueList.add("'" + item.get("password") + "'");
            String sql = "INSERT INTO userinfo(name, age, password) VALUE (" + StringUtils.join(valueList, ",") + ")";
            statement1.addBatch(sql);
            i1++;
            if (i1 == 1000) {
                statement1.executeBatch();
                connection.commit();
            }
        }
        statement1.executeBatch();
        connection.commit();
        long end1 = System.currentTimeMillis();
        System.out.println("insert " + list.size() + "rows cast:" + (end1 - start1));
    }
}
